﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;

namespace MarginTrading.Services
{
    public class AgentServices
    {
        private static Agent GetAgentFromDataReader(IDataReader dataReader)
        {
            Agent retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Agent();
                retVal.Code = dataReader["CODE"].ToString();
                retVal.Name = dataReader["NAME"].ToString();
                retVal.Address_1 = dataReader["ADDRESS_1"].ToString();
                retVal.Address_2 = dataReader["ADDRESS_2"].ToString();
                retVal.Address_3 = dataReader["ADDRESS_3"].ToString();
                retVal.Address_4 = dataReader["ADDRESS_4"].ToString();
                retVal.Telephone1 = dataReader["TELEPHONE_1"].ToString();
                retVal.Telephone2 = dataReader["TELEPHONE_2"].ToString();
                retVal.Fax = dataReader["FAX"].ToString();
                retVal.Interest_Rate = double.Parse(dataReader["INTEREST_RATE"].ToString());
                DateTime createdDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["CREATED_DATE"].ToString(), out createdDate))
                    retVal.CreatedDate = createdDate;
            }
            return retVal;
        }

        public static Agent GetAgentByCode(string code)
        {
            Agent retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from agent where code = '" + code + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetAgentFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static AgentCollection GetAgentCollection()
        {
            AgentCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from agent";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AgentCollection();
                    while (dataReader.Read())
                    {
                        Agent item = GetAgentFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetAgentCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from agent";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Agent agent)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into agent(CODE,NAME,ADDRESS_1,ADDRESS_2,ADDRESS_3," +
                                  "ADDRESS_4,TELEPHONE_1,TELEPHONE_2,FAX,CREATED_DATE, INTEREST_RATE)" +
                                  "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10, :v11)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = agent.Name;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = agent.Address_1;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = agent.Address_2;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = agent.Address_3;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = agent.Address_4;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = agent.Telephone1;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = agent.Telephone1;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = agent.Fax;
                cmd.Parameters.Add(":v10", OracleType.DateTime).Value = agent.CreatedDate;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = agent.Interest_Rate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string code)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from agent where code = '" + code + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Agent agent)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update agent set " +
                                  "NAME = :v2 ," +
                                  "ADDRESS_1 = :v3 ," +
                                  "ADDRESS_2 = :v4 ," +
                                  "ADDRESS_3 = :v5 ," +
                                  "ADDRESS_4 = :v6 ," +
                                  "TELEPHONE_1 = :v7 ," +
                                  "TELEPHONE_2 = :v8 ," +
                                  "FAX = :v9 ," +
                                  "CREATED_DATE = :v10, INTEREST_RATE = :v11 where CODE = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = agent.Name;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = agent.Address_1;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = agent.Address_2;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = agent.Address_3;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = agent.Address_4;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = agent.Telephone1;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = agent.Telephone1;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = agent.Fax;
                cmd.Parameters.Add(":v10", OracleType.DateTime).Value = agent.CreatedDate;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = agent.Interest_Rate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
